/*
 * Copyright (c) 2025 Eclipse Dirigible contributors
 *
 * All rights reserved. This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v2.0 which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v20.html
 *
 * SPDX-FileCopyrightText: Eclipse Dirigible contributors SPDX-License-Identifier: EPL-2.0
 */
package org.eclipse.dirigible.database.sql.dialects.hana;

import org.eclipse.dirigible.database.sql.DatabaseArtifactTypes;
import org.eclipse.dirigible.database.sql.ISqlKeywords;
import org.eclipse.dirigible.database.sql.builders.records.DeleteBuilder;
import org.eclipse.dirigible.database.sql.builders.records.InsertBuilder;
import org.eclipse.dirigible.database.sql.builders.records.SelectBuilder;
import org.eclipse.dirigible.database.sql.builders.records.UpdateBuilder;
import org.eclipse.dirigible.database.sql.dialects.DefaultSqlDialect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashSet;
import java.util.Set;

/**
 * The HANA SQL Dialect.
 */
public class HanaSqlDialect extends
        DefaultSqlDialect<SelectBuilder, InsertBuilder, UpdateBuilder, DeleteBuilder, HanaCreateBranchingBuilder, HanaAlterBranchingBuilder, HanaDropBranchingBuilder, HanaNextValueSequenceBuilder, HanaLastValueIdentityBuilder>
        implements DatabaseArtifactTypes {

    /** The Constant IDENTITY_ARGUMENT. */
    private static final String IDENTITY_ARGUMENT = "GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1)";

    /** The Constant logger. */
    private static final Logger logger = LoggerFactory.getLogger(HanaSqlDialect.class);

    /**
     * Checks if is sequence existing.
     *
     * @param connection the connection
     * @param schema the schema
     * @param sequence the sequence
     * @return true, if is sequence existing
     * @throws SQLException the SQL exception
     */
    private boolean isSequenceExisting(Connection connection, String schema, String sequence) throws SQLException {
        String sql;
        if (schema != null) {
            sql = "ALTER SEQUENCE \"" + schema + "\"" + ".\"" + sequence + "\"";
        } else {
            sql = "ALTER SEQUENCE \"" + sequence + "\"";
        }
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            try {
                statement.executeUpdate();
                return true;
            } catch (Exception e) {
                return false;
            }
        }
    }

    /**
     * Checks if is procedure existing.
     *
     * @param connection the connection
     * @param schemaPattern the schema pattern
     * @param procedure the procedure
     * @return true, if is procedure existing
     * @throws SQLException the SQL exception
     */
    private boolean isProcedureExisting(Connection connection, String schemaPattern, String procedure) throws SQLException {
        DatabaseMetaData metadata = connection.getMetaData();
        ResultSet procedureDescription = metadata.getProcedures(null, schemaPattern, procedure);
        return procedureDescription.next();
    }

    /**
     * Checks if is function existing.
     *
     * @param connection the connection
     * @param schemaPattern the schema pattern
     * @param function the function
     * @return true, if is function existing
     * @throws SQLException the SQL exception
     */
    private boolean isFunctionExisting(Connection connection, String schemaPattern, String function) throws SQLException {
        DatabaseMetaData metadata = connection.getMetaData();
        ResultSet funcDescription = metadata.getFunctions(null, schemaPattern, function);
        return funcDescription.next();
    }

    /**
     * Checks if is synonym existing.
     *
     * @param connection the connection
     * @param schemaPattern the schema pattern
     * @param synonymPattern the synonym pattern
     * @return true, if is synonym existing
     * @throws SQLException the SQL exception
     */
    private boolean isSynonymExisting(Connection connection, String schemaPattern, String synonymPattern) throws SQLException {
        DatabaseMetaData metadata = connection.getMetaData();
        ResultSet funcDescription = metadata.getTables(null, schemaPattern, synonymPattern, new String[] {ISqlKeywords.KEYWORD_SYNONYM});
        return funcDescription.next();
    }

    /**
     * Checks if is schema existing.
     *
     * @param connection the connection
     * @param schema the schema
     * @return true, if is schema existing
     * @throws SQLException the SQL exception
     */
    private boolean isSchemaExisting(Connection connection, String schema) throws SQLException {
        DatabaseMetaData metadata = connection.getMetaData();
        ResultSet funcDescription = metadata.getSchemas(null, schema);
        return funcDescription.next();
    }

    /**
     * Checks if is table type existing.
     *
     * @param connection the connection
     * @param schema the schema
     * @param tableType the table type
     * @return true, if is table type existing
     * @throws SQLException the SQL exception
     */
    private boolean isTableTypeExisting(Connection connection, String schema, String tableType) throws SQLException {
        String sql;
        if (schema != null) {
            sql = "SELECT IS_USER_DEFINED_TYPE FROM SYS.\"" + "TABLES" + "\" WHERE TABLE_NAME LIKE '" + tableType
                    + "' AND IS_USER_DEFINED_TYPE LIKE " + "'TRUE' AND SCHEMA_NAME LIKE '" + schema + "'";
        } else {
            sql = "SELECT IS_USER_DEFINED_TYPE FROM SYS.\"" + "TABLES" + "\" WHERE TABLE_NAME LIKE '" + tableType
                    + "' AND IS_USER_DEFINED_TYPE LIKE " + "'TRUE'";
        }
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            String value = null;
            try {
                try (ResultSet resultSet = statement.executeQuery()) {
                    while (resultSet.next()) {
                        value = resultSet.getString(1);
                    }
                    return value.equals("TRUE");
                }
            } catch (Exception e) {
                return false;
            }
        }
    }

    /** The Constant FUNCTIONS. */
    public static final Set<String> FUNCTIONS = Collections.synchronizedSet(new HashSet<String>(Arrays.asList("abap_alphanum", "abap_numc",
            "abap_lower", "abap_upper", "abs", "acos", "add_days", "add_months", "add_months_last", "add_nano100", "add_seconds",
            "add_workdays", "add_years", "ascii", "asin", "atan", "atan2", "auto_corr", "bintohex", "bintonhex", "bintostr", "bitand",
            "bitcount", "bitnot", "bitor", "bitset", "bitunset", "bitxor", "cardinality", "cast", "ceil", "char", "coalesce", "concat",
            "concat_naz", "convert_currency", "convert_unit", "corr", "corr_spearman", "cos", "cosh", "cot", "cross_corr",
            "current_connection", "current_date", "current_identity_value", "current_mvcc_snapshot_timestamp", "current_object_schema",
            "current_schema", "current_time", "current_timestamp", "current_transaction_isolation_level",
            "current_update_statement_sequence", "current_update_transaction", "current_user", "current_utcdate", "current_utctime",
            "current_utctimestamp", "dayname", "dayofmonth", "dayofyear", "days_between", "dft", "encryption_root_keys_extract_keys",
            "encryption_root_keys_has_backup_password", "escape_double_quotes", "escape_single_quotes", "exp", "expression_macro",
            "extract", "first_value", "floor", "generate_password", "greatest", "grouping", "grouping_id", "hamming_distance", "hash_md5",
            "hash_sha256", "hextobin", "hour", "ifnull", "indexing_error_code", "indexing_error_message", "indexing_status", "initcap",
            "is_sql_injection_safe", "isoweek", "json_query", "json_table", "json_value", "language", "last_day", "last_value", "lcase",
            "least", "left", "length", "ln", "localtoutc", "locate", "locate_regexpr", "log", "lower", "lpad", "ltrim", "map", "median",
            "member_at", "mimetype", "minute", "mod", "month", "monthname", "months_between", "nano100_between", "nchar", "ndiv0",
            "next_day", "newuid", "normalize", "now", "nth_value", "nullif", "occurrences_regexpr", "plaintext", "power", "quarter", "rand",
            "rand_secure", "record_commit_timestamp", "record_id", "replace", "replace_regexpr", "result_cache_id",
            "result_cache_refresh_time", "right", "round", "rpad", "rtrim", "score", "second", "seconds_between", "series_disaggregate",
            "series_element_to_period", "series_generate", "series_period_to_element", "series_round", "session_context", "session_user",
            "sign", "sin", "sinh", "soundex", "sqrt", "stddev_pop", "stddev_samp", "string_agg", "strtobin", "subarray", "substr_after",
            "substr_before", "substring_regexpr", "substring", "sysuuid", "tan", "tanh", "to_alphanum", "to_bigint", "to_binary", "to_blob",
            "to_boolean", "to_clob", "to_date", "to_dats", "to_decimal", "to_double", "to_fixedchar", "to_int", "to_integer",
            "to_json_boolean", "to_nclob", "to_nvarchar", "to_real", "to_seconddate", "to_smalldecimal", "to_smallint", "to_time",
            "to_timestamp", "to_tinyint", "to_varchar", "trim", "trim_array", "ucase", "uminus", "unicode", "upper", "utctolocal",
            "var_pop", "var_samp", "week", "weekday", "width_bucket", "workdays_between", "xmlextract", "xmlextractvalue", "xmltable",
            "year", "years_between",

            "count", "sum", "avg", "min", "max",

            "and", "or", "between", "binary", "case", "div", "in", "is", "not", "null", "like", "rlike", "xor")));

    /**
     * Nextval.
     *
     * @param sequence the sequence
     * @return the hana next value sequence builder
     */
    @Override
    public HanaNextValueSequenceBuilder nextval(String sequence) {
        return new HanaNextValueSequenceBuilder(this, sequence);
    }

    /**
     * Creates the.
     *
     * @return the hana create branching builder
     */
    @Override
    public HanaCreateBranchingBuilder create() {
        return new HanaCreateBranchingBuilder(this);
    }

    /**
     * Drop.
     *
     * @return the hana drop branching builder
     */
    @Override
    public HanaDropBranchingBuilder drop() {
        return new HanaDropBranchingBuilder(this);
    }

    /**
     * Alter.
     *
     * @return the hana alter branching builder
     */
    @Override
    public HanaAlterBranchingBuilder alter() {
        return new HanaAlterBranchingBuilder(this);
    }

    /**
     * Gets the identity argument.
     *
     * @return the identity argument
     */
    @Override
    public String getIdentityArgument() {
        return IDENTITY_ARGUMENT;
    }

    /**
     * Lastval.
     *
     * @param args the args
     * @return the hana last value identity builder
     */
    @Override
    public HanaLastValueIdentityBuilder lastval(String... args) {
        return new HanaLastValueIdentityBuilder(this, args);
    }

    /**
     * Exists.
     *
     * @param connection the connection
     * @param table the table
     * @return true, if successful
     * @throws SQLException the SQL exception
     */
    @Override
    public boolean existsTable(Connection connection, String table) throws SQLException {
        return exists(connection, table, DatabaseArtifactTypes.TABLE);
    }

    /**
     * Exists.
     *
     * @param connection the connection
     * @param artefact the artefact
     * @param type the type
     * @return true, if successful
     * @throws SQLException the SQL exception
     */
    @Override
    public boolean exists(Connection connection, String artefact, int type) throws SQLException {
        return exists(connection, null, artefact, type);
    }

    /**
     * Exists.
     *
     * @param connection the connection
     * @param schema the schema
     * @param artefact the artefact
     * @param type the type
     * @return true, if successful
     * @throws SQLException the SQL exception
     */
    @Override
    public boolean exists(Connection connection, String schema, String artefact, int type) throws SQLException {
        try {
            switch (type) {
                case DatabaseArtifactTypes.TABLE:
                case DatabaseArtifactTypes.VIEW:
                    return count(connection, schema, artefact) >= 0;
                case DatabaseArtifactTypes.SYNONYM:
                    return isSynonymExisting(connection, schema, artefact);
                case DatabaseArtifactTypes.FUNCTION:
                    return isFunctionExisting(connection, schema, artefact);
                case DatabaseArtifactTypes.PROCEDURE:
                    return isProcedureExisting(connection, schema, artefact);
                case DatabaseArtifactTypes.SEQUENCE:
                    return isSequenceExisting(connection, schema, artefact);
                case DatabaseArtifactTypes.SCHEMA:
                    return isSchemaExisting(connection, artefact);
                case DatabaseArtifactTypes.TABLE_TYPE:
                    return isTableTypeExisting(connection, schema, artefact);
                default:
                    throw new IllegalArgumentException("Cannot check existence of artifact [" + artefact + "] in schema [" + schema
                            + "] because type [" + type + "] is not supporeted.");
            }
        } catch (Exception ex) {
            // Do nothing, because the artifact do not exist
            logger.debug("Assuming artifact [{}] in schema  [{}] of type [{}] does not exist", artefact, schema, type, ex);
            return false;
        }
    }

    /**
     * Checks if is schema filter supported.
     *
     * @return true, if is schema filter supported
     */
    @Override
    public boolean isSchemaFilterSupported() {
        return true;
    }

    /**
     * Gets the schema filter script.
     *
     * @return the schema filter script
     */
    @Override
    public String getSchemaFilterScript() {
        return "SELECT * FROM \"SYS\".\"SCHEMAS\"";
    }

    /**
     * Gets the functions names.
     *
     * @return the functions names
     */
    @Override
    public Set<String> getFunctionsNames() {
        return FUNCTIONS;
    }

    /**
     * Gets the fuzzy search index.
     *
     * @return the fuzzy search index
     */
    @Override
    public String getFuzzySearchIndex() {
        return "FUZZY SEARCH INDEX ON";
    }

}

